package com.cyj.springbootservice.controller.mybatisplus;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.cyj.springbootservice.api.entity.Employee;
import com.cyj.springbootservice.mapper.EmployeeMapper;
import lombok.AllArgsConstructor;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @Author C_Y_J
 * @create 2021/10/7 9:45
 **/
@RestController
@AllArgsConstructor
@RequestMapping("/employee")
public class EmployeeController {

    private EmployeeMapper employeeMapper;

    /**
     * 通用 插入操作
     */
    @PostMapping("/insert")
    public void commonInsert() {

        Employee employee = new Employee();
        employee.setLastName("MP");
        employee.setEmail("mp@qq.com");
        employee.setGender(1);
        employee.setAge(22);

        // insert方法在插入时， 会根据实体类的每个属性进行非空判断，只有非空的属性对应的字段才会出现到SQL语句中
        System.out.println("插入成功的条数：" + employeeMapper.insert(employee));

        //获取当前数据在数据库中的主键值
        System.out.println("插入成功后返回的主键值：" + employee.getId());
    }

    /**
     * 条件构造器  查询操作
     */
    @GetMapping("/select")
    public void commonSelect() {
        //1. 通过id查询
        System.out.println(employeeMapper.selectById(1));
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE id=?


        //2. 通过多个列进行查询  id  +  lastName  查询结果为多个
        System.out.println(employeeMapper.selectOne(new QueryWrapper<Employee>()
                .eq("id", "7")
                .eq("last_name", "小泽老师")
        ));
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE (id = ? AND last_name = ?)
        //==>  Parameters: 7(String), 小泽老师(String)


        //3. 查询结果为多个
        System.out.println(employeeMapper.selectList(new QueryWrapper<Employee>()
                .eq("last_name", "小泽老师")
        ));
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE (last_name = ?)
        //==>  Parameters: 小泽老师(String)


        //4. 通过多个id进行查询
        List<Integer> idsList = Arrays.asList(2, 3, 4, 5);
        System.out.println(employeeMapper.selectBatchIds(idsList));
        //==> Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE id IN ( ? , ? , ? , ? )
        //==> Parameters: 2(Integer), 3(Integer), 4(Integer), 5(Integer)


        //5. 通过Map封装条件查询
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("last_name", "Tom");
        columnMap.put("gender", 1);
        System.out.println(employeeMapper.selectByMap(columnMap));
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE gender = ? AND last_name = ?
        //==>  Parameters: 1(Integer), Tom(String)


        //6. 分页查询
        System.out.println(employeeMapper.selectPage(new Page<Employee>(2, 2), null).getRecords());
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee


        //7. 分页查询t_employee表中，年龄在18~50之间且性别为男且姓名为Tom的所有用户
        System.out.println(employeeMapper.selectPage(new Page<Employee>(2, 2),
                new QueryWrapper<Employee>()
                        .between("age", 18, 50)
                        .eq("gender", 1)
                        .eq("last_name", "Tom")
                ).getRecords()
        );
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE (age BETWEEN ? AND ? AND gender = ? AND last_name = ?)
        //==>  Parameters: 18(Integer), 50(Integer), 1(Integer), Tom(String)


        //8. 查询t_employee表中， 性别为女并且名字中带有"老师" 或者  邮箱中带有"a"
        System.out.println(employeeMapper.selectList(
                new QueryWrapper<Employee>()
                        .eq("gender", 0)
                        .like("last_name", "老师")
                        .or()    // SQL: (gender = ? AND last_name LIKE ? OR email LIKE ?)
                        .like("email", "a")
        ));
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE (gender = ? AND last_name LIKE ? OR email LIKE ?)
        //==>  Parameters: 0(Integer), %老师%(String), %a%(String)


        //9. 查询性别为女的, 根据age进行排序(asc/desc), 简单分页
        System.out.println(employeeMapper.selectPage(
                new Page<Employee>(1, 3),
                new QueryWrapper<Employee>()
                        .eq("gender", 0)
                        .orderByAsc("gender")
        ).getRecords());
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE (gender = ?) ORDER BY gender ASC
        //==> Parameters: 0(Integer)
        System.out.println(employeeMapper.selectPage(
                new Page<Employee>(1, 3),
                new QueryWrapper<Employee>()
                        .eq("gender", 0)
                        .orderByDesc("gender")
        ).getRecords());
        //==>  Preparing: SELECT id,last_name,gender,age,email FROM t_employee WHERE (gender = ?) ORDER BY gender DESC
        //==>  Parameters: 0(Integer)
    }

    /**
     * 通用 更新操作
     */
    @PostMapping("/update")
    public void commonUpdate() {
        Employee employee = new Employee();
        employee.setId(7);
        employee.setLastName("小泽老师");
        employee.setEmail("xz@sina.com");
        employee.setGender(0);
        employee.setAge(33);
        //updateById 根据id去修改
        Integer result = employeeMapper.updateById(employee);
        //==>  Preparing: UPDATE t_employee SET last_name=?, gender=?, age=?, email=? WHERE id=?
        //==>  Preparing: UPDATE t_employee SET last_name=?, gender=?, age=?, email=? WHERE id=?


        Employee employee2 = new Employee();
        employee2.setEmail("xz@sina.com");
        employee2.setGender(0);
        employee2.setAge(33);
        //update 根据条件构造去修改
        employeeMapper.update(employee2, new UpdateWrapper<Employee>()
                .eq("last_name", "White")
        );
        //==>  Preparing: UPDATE t_employee SET gender=?, age=?, email=? WHERE (last_name = ?)
        //==>  Parameters: 0(Integer), 33(Integer), xz@sina.com(String), White(String)

        Employee employee3 = new Employee();
        employee3.setLastName("苍老师");
        employee3.setEmail("cls@sina.com");
        employee3.setGender(0);
        employeeMapper.update(employee3,
                new UpdateWrapper<Employee>()
                        .eq("last_name", "Tom")
                        .eq("age", 44)
        );
        //==>  Preparing: UPDATE t_employee SET last_name=?, gender=?, email=? WHERE (last_name = ? AND age = ?)
        //==> Parameters: 苍老师(String), 0(Integer), cls@sina.com(String), Tom(String), 44(Integer)
    }

    /**
     * 通用 条件构造器 删除操作
     */
    @DeleteMapping("/delete")
    public void commonDelete() {
        //1. 根据id进行删除
        employeeMapper.deleteById(9);
        //==>  Preparing: DELETE FROM t_employee WHERE id=?
        //==>  Parameters: 9(Integer)


        //2. 根据 条件进行删除
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("last_name", "MP");
        columnMap.put("email", "mp@qq.com");
        employeeMapper.deleteByMap(columnMap);
        //==>  Preparing: DELETE FROM t_employee WHERE last_name = ? AND email = ?
        //==>  Parameters: MP(String), mp@qq.com(String)


        //3. 根据 条件进行删除
        employeeMapper.delete(
                new UpdateWrapper<Employee>()
                        .eq("last_name", "Tom")
                        .eq("age", 22));
        //==>  Preparing: DELETE FROM t_employee WHERE (last_name = ? AND age = ?)
        //==>  Parameters: Tom(String), 22(Integer)


        //4. 通过 id 批量删除
        List<Integer> idsList = Arrays.asList(2, 3, 4, 7);
        employeeMapper.deleteBatchIds(idsList);
        //==>  Preparing: DELETE FROM t_employee WHERE id IN ( ? , ? , ? , ? )
        //==>  Parameters: 2(Integer), 3(Integer), 4(Integer), 7(Integer)

    }
}
